Namespace persistent
    Public Class sirket_yetkilisi

       

        Public Shared Function get_sirket_yetkilisi(ByVal custcode As String) As DataTable
            Dim strSQL As String

            'strSQL = " select customer_id,tmcode from customer_all where custcode = '" & custcode & "' "
            'strSQL += " and cstype in ('a','s') and (  "
            'strSQL += " (prgcode = '29' and cslevel in (20,30) ) or "
            'strSQL += " (prgcode in ('24','25','26','27','30','31','33','34') and cslevel=40)) "

            strSQL = " select (select fn_custid_to_msisdn(t1.co_id) from dual) msisdn,t1.co_id,cc.ccfname||' '||cclname isim,t2.servis_statu from "
            strSQL += " (select co.co_id,cs.customer_id from customer_all cs,contract_all co "
            strSQL += " where custcode like '" + custcode + "%' "
            strSQL += " and cs.customer_id=co.customer_id(+)) t1, "
            strSQL += " (select co_id,substr(cs_stat_chng,-1) servis_statu from contr_services cs "
            strSQL += " where cs_seqno=(select max(cs_seqno) from contr_services where co_id=cs.co_id) and sncode=276) t2,ccontact_all cc "
            strSQL += " where t1.co_id=t2.co_id and "
            strSQL += " t1.customer_id=cc.customer_id and cc.CCBILL='X' order by t2.servis_statu "

            Return myDBConn.myORATable(strSQL)
        End Function


        Public Shared Function GetContracts(ByVal custcode As String) As DataTable
            Dim sql As String

            sql = " select fn_coid_to_msisdn(co.co_id) ms,co.co_id ,curr.CH_STATUS statu "
            sql += " from customer_all cu, contract_all co,curr_co_status curr  "
            sql += " where (cu.custcode like  '" + custcode + ".%' or cu.custcode='" + custcode + "' )  "
            sql += " and cu.customer_id=co.customer_id(+) "
            sql += " and co.co_id=curr.co_id"
            sql += " and cu.cslevel='40'"

            Return myDBConn.myORATable(sql)

        End Function

    End Class

End Namespace
